﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SQLite;

namespace Amgine
{
    static class DBHelper
    {

        #region db const

        public const string PKID = "_id";
        public const string TABLE_ACCOUNT = "tb_account";
        public const string TABLE_ACCOUNT_GROUP = "tb_account_group";
        public const string TABLE_TOOL = "tb_tool";
        public const string TABLE_TOOL_GROUP = "tb_tool_group";
        public const string TABLE_LINKMAN_GROUP = "tb_linkman_group";
        public const string TABLE_LINKMAN = "tb_linkman";
        public const string TABLE_LINKMAN_DETAILS = "tb_linkman_details";
        public const string TABLE_DICTIONARY = "tb_dictionary";
        public const string TABLE_NOTE = "tb_note";

        public const string ACCOUNT_TITLE = "title";
        public const string ACCOUNT_NAME = "name";
        public const string ACCOUNT_PASSWORD = "psw";
        public const string ACCOUNT_REMARK = "remark";
        public const string ACCOUNT_GROUP_ID = "group_id";
        public const string ACCOUNT_GROUP_TITLE = "title";
        public const string ACCOUNT_GROUP_REMARK = "remark";

        public const string TOOL_NAME = "name";
        public const string TOOL_REMARK = "remark";
        public const string TOOL_GROUP_ID = "group_id";
        public const string TOOL_GROUP_TITLE = "title";
        public const string TOOL_GROUP_REMARK = "remark";

        public const string LINKMAN_GROUP_NAME = "name";
        public const string LINKMAN_GROUP_REMARK = "remark";
        public const string LINKMAN_GROUP_ID = "group_id";
        public const string LINKMAN_NAME = "name";
        public const string LINKMAN_REMARK = "remark";
        public const string LINKMAN_DETAILS_TYPE = "type";
        public const string LINKMAN_DETAILS_INFO = "info";
        public const string LINKMAN_DETAILS_REMARK = "remark";
        public const string LINKMAN_ID = "linkman_id";

        public const string DICTIONARY_TYPE = "type";
        public const string DICTIONARY_CODE = "code";
        public const string DICTIONARY_INFO = "info";
        public const string DICTIONARY_IS_ENCRYPTED = "is_encrypted";
        public const string NOTE_TITLE = "title";
        public const string NOTE_INFO = "info";
        public const string NOTE_CREATE_TIME = "create_time";
        public const string NOTE_UPDATE_TIME = "update_time";

        #endregion

        public static SQLiteConnection BASE_SQLITE_CONNECTION;

        public static void initDB()
        {
            BASE_SQLITE_CONNECTION = new SQLiteConnection("Data Source ="
                + Environment.CurrentDirectory + "/data.db");
            BASE_SQLITE_CONNECTION.Open();

            using (SQLiteTransaction tran = BASE_SQLITE_CONNECTION.BeginTransaction())
            {
                SQLiteCommand cmd = new SQLiteCommand(BASE_SQLITE_CONNECTION);
                cmd.Transaction = tran;

                cmd.CommandText = " CREATE TABLE IF NOT EXISTS " + TABLE_ACCOUNT
                    + " ( " + PKID + " INTEGER PRIMARY KEY, "
                    + ACCOUNT_TITLE + " TEXT, "
                    + ACCOUNT_NAME + " TEXT, "
                    + ACCOUNT_PASSWORD + " TEXT, "
                    + ACCOUNT_REMARK + " TEXT, "
                    + ACCOUNT_GROUP_ID + " INTEGER); ";
                cmd.ExecuteNonQuery();

                cmd.CommandText = " CREATE TABLE IF NOT EXISTS " + TABLE_ACCOUNT_GROUP
                    + " ( " + PKID + " INTEGER PRIMARY KEY, "
                    + ACCOUNT_GROUP_TITLE + " TEXT, "
                    + ACCOUNT_GROUP_REMARK + " TEXT); ";
                cmd.ExecuteNonQuery();

                cmd.CommandText = " CREATE TABLE IF NOT EXISTS " + TABLE_TOOL
                    + " ( " + PKID + " INTEGER PRIMARY KEY, "
                    + TOOL_NAME + " TEXT, "
                    + TOOL_REMARK + " TEXT, "
                    + TOOL_GROUP_ID + " INTEGER); ";
                cmd.ExecuteNonQuery();

                cmd.CommandText = " CREATE TABLE IF NOT EXISTS " + TABLE_TOOL_GROUP
                    + " ( " + PKID + " INTEGER PRIMARY KEY, "
                    + TOOL_GROUP_TITLE + " TEXT, "
                    + TOOL_GROUP_REMARK + " TEXT); ";
                cmd.ExecuteNonQuery();

                cmd.CommandText = " CREATE TABLE IF NOT EXISTS " + TABLE_LINKMAN_GROUP
                    + " ( " + PKID + " INTEGER PRIMARY KEY, "
                    + LINKMAN_GROUP_NAME + " TEXT, "
                    + LINKMAN_GROUP_REMARK + " TEXT); ";
                cmd.ExecuteNonQuery();

                cmd.CommandText = " CREATE TABLE IF NOT EXISTS " + TABLE_LINKMAN
                    + " ( " + PKID + " INTEGER PRIMARY KEY, "
                    + LINKMAN_GROUP_ID + " INTEGER, "
                    + LINKMAN_NAME + " TEXT, "
                    + LINKMAN_REMARK + " TEXT); ";
                cmd.ExecuteNonQuery();

                cmd.CommandText = " CREATE TABLE IF NOT EXISTS " + TABLE_LINKMAN_DETAILS
                    + " ( " + PKID + " INTEGER PRIMARY KEY, "
                    + LINKMAN_DETAILS_TYPE + " INTEGER, "
                    + LINKMAN_DETAILS_INFO + " TEXT, "
                    + LINKMAN_DETAILS_REMARK + " TEXT, "
                    + LINKMAN_ID + " INTEGER); ";
                cmd.ExecuteNonQuery();

                cmd.CommandText = " CREATE TABLE IF NOT EXISTS " + TABLE_DICTIONARY
                    + " ( " + PKID + " INTEGER PRIMARY KEY, "
                    + DICTIONARY_TYPE + " INTEGER, "
                    + DICTIONARY_CODE + " TEXT, "
                    + DICTIONARY_INFO + " TEXT, "
                    + DICTIONARY_IS_ENCRYPTED + " INTEGER); ";
                cmd.ExecuteNonQuery();

                cmd.CommandText = " CREATE TABLE IF NOT EXISTS " + TABLE_NOTE
                    + " ( " + PKID + " INTEGER PRIMARY KEY, "
                    + NOTE_TITLE + " TEXT, "
                    + NOTE_INFO + " TEXT, "
                    + NOTE_CREATE_TIME + " TEXT, "
                    + NOTE_UPDATE_TIME + " TEXT); ";
                cmd.ExecuteNonQuery();

                tran.Commit();
            }
        }

        #region list all

        public static List<Note> listAllNote()
        {
            SQLiteCommand cmd = new SQLiteCommand(" SELECT * FROM "
                + TABLE_NOTE + " ; ", BASE_SQLITE_CONNECTION);
            SQLiteDataReader reader = cmd.ExecuteReader();

            Note note = null;
            List<Note> list = new List<Note>();

            while (reader.Read())
            {
                note = new Note();
                note.ID = reader.GetInt32(0);
                note.Title = reader.GetString(1);
                note.Info = reader.GetString(2);
                note.CreateTime = reader.GetString(3);
                note.UpdateTime = reader.GetString(4);
                list.Add(note);
            }

            return list;
        }

        public static List<Dictionary> listAllDictionary()
        {
            SQLiteCommand cmd = new SQLiteCommand(" SELECT * FROM "
                + TABLE_DICTIONARY + " ; ", BASE_SQLITE_CONNECTION);
            SQLiteDataReader reader = cmd.ExecuteReader();

            bool flag = false;
            string str = null;
            Dictionary dictionary = null;
            List<Dictionary> list = new List<Dictionary>();

            while (reader.Read())
            {
                dictionary = new Dictionary();
                flag = (reader.GetInt32(4) != 0);
                dictionary.ID = reader.GetInt32(0);
                dictionary.Type = reader.GetInt32(1);
                str = reader.GetString(2);
                dictionary.Code = flag ? EncryptDecryptUtils.DES3Decrypt(str, EncryptDecryptUtils.GLOBAL_INIT_VAR) : str;
                str = reader.GetString(3);
                dictionary.Info = flag ? EncryptDecryptUtils.DES3Decrypt(str, EncryptDecryptUtils.GLOBAL_INIT_VAR) : str;
                dictionary.IsEncrypted = flag;
                list.Add(dictionary);
            }

            return list;
        }

        public static Hashtable listAllTools()
        {
            SQLiteCommand cmd = new SQLiteCommand(
                " SELECT * FROM " + TABLE_TOOL + " ; ", BASE_SQLITE_CONNECTION);
            SQLiteDataReader reader = cmd.ExecuteReader();

            Tool tool = null;
            Hashtable list = new Hashtable();

            while (reader.Read())
            {
                tool = new Tool();
                tool.ID = reader.GetInt32(0);
                tool.Name = reader.GetString(1);
                tool.Remark = reader.GetString(2);
                tool.GroupId = reader.GetInt32(3);

                if (!list.Contains(tool.GroupId))
                {
                    list[tool.GroupId] = new List<Tool>();
                }
                ((List<Tool>)list[tool.GroupId]).Add(tool);
            }

            return list;
        }

        public static List<Tool> listAllToolsFromDB()
        {
            SQLiteCommand cmd = new SQLiteCommand(
                " SELECT * FROM " + TABLE_TOOL + " ; ", BASE_SQLITE_CONNECTION);
            SQLiteDataReader reader = cmd.ExecuteReader();

            Tool tool = null;
            List<Tool> list = new List<Tool>();

            while (reader.Read())
            {
                tool = new Tool();
                tool.ID = reader.GetInt32(0);
                tool.Name = reader.GetString(1);
                tool.Remark = reader.GetString(2);
                tool.GroupId = reader.GetInt32(3);
                list.Add(tool);
            }

            return list;
        }

        public static List<ToolGroup> listAllToolGroup()
        {
            SQLiteCommand cmd = new SQLiteCommand(" SELECT * FROM "
                + TABLE_TOOL_GROUP + " ; ", BASE_SQLITE_CONNECTION);
            SQLiteDataReader reader = cmd.ExecuteReader();

            ToolGroup group = null;
            List<ToolGroup> list = new List<ToolGroup>();

            while (reader.Read())
            {
                group = new ToolGroup();
                group.ID = reader.GetInt32(0);
                group.Title = reader.GetString(1);
                group.Remark = reader.GetString(2);
                list.Add(group);
            }

            return list;
        }

        public static Hashtable listAllLinkman()
        {
            SQLiteCommand cmd = new SQLiteCommand(" SELECT * FROM "
                + TABLE_LINKMAN + " ; ", BASE_SQLITE_CONNECTION);
            SQLiteDataReader reader = cmd.ExecuteReader();

            Linkman linkman = null;
            Hashtable list = new Hashtable();

            while (reader.Read())
            {
                linkman = new Linkman();
                linkman.ID = reader.GetInt32(0);
                linkman.GroupId = reader.GetInt32(1);
                linkman.Name = EncryptDecryptUtils.DES3Decrypt(reader.GetString(2), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                linkman.Remark = EncryptDecryptUtils.DES3Decrypt(reader.GetString(3), EncryptDecryptUtils.GLOBAL_INIT_VAR);

                if (!list.Contains(linkman.GroupId))
                {
                    list[linkman.GroupId] = new List<Linkman>();
                }
                ((List<Linkman>)list[linkman.GroupId]).Add(linkman);
            }

            return list;
        }

        public static List<Linkman> listAllLinkmanFromDB()
        {
            SQLiteCommand cmd = new SQLiteCommand(" SELECT * FROM "
                + TABLE_LINKMAN + " ; ", BASE_SQLITE_CONNECTION);
            SQLiteDataReader reader = cmd.ExecuteReader();

            Linkman linkman = null;
            List<Linkman> list = new List<Linkman>();

            while (reader.Read())
            {
                linkman = new Linkman();
                linkman.ID = reader.GetInt32(0);
                linkman.GroupId = reader.GetInt32(1);
                linkman.Name = EncryptDecryptUtils.DES3Decrypt(reader.GetString(2), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                linkman.Remark = EncryptDecryptUtils.DES3Decrypt(reader.GetString(3), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                list.Add(linkman);
            }

            return list;
        }

        public static List<LinkmanGroup> listAllLinkmanGroup()
        {
            SQLiteCommand cmd = new SQLiteCommand(" SELECT * FROM "
                + TABLE_LINKMAN_GROUP + " ; ", BASE_SQLITE_CONNECTION);
            SQLiteDataReader reader = cmd.ExecuteReader();

            LinkmanGroup group = null;
            List<LinkmanGroup> list = new List<LinkmanGroup>();

            while (reader.Read())
            {
                group = new LinkmanGroup();
                group.ID = reader.GetInt32(0);
                group.Name = EncryptDecryptUtils.DES3Decrypt(reader.GetString(1), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                group.Remark = EncryptDecryptUtils.DES3Decrypt(reader.GetString(2), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                list.Add(group);
            }

            return list;
        }

        public static Hashtable listAllLinkmanDetails()
        {
            SQLiteCommand cmd = new SQLiteCommand(" SELECT * FROM " + TABLE_LINKMAN_DETAILS
                + " ORDER BY " + LINKMAN_DETAILS_TYPE + " ; ", BASE_SQLITE_CONNECTION);
            SQLiteDataReader reader = cmd.ExecuteReader();

            LinkmanDetails detail = null;
            Hashtable details = new Hashtable();

            while (reader.Read())
            {
                detail = new LinkmanDetails();
                detail.ID = reader.GetInt32(0);
                detail.Type = reader.GetInt32(1);
                detail.Info = EncryptDecryptUtils.DES3Decrypt(reader.GetString(2), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                detail.Remark = EncryptDecryptUtils.DES3Decrypt(reader.GetString(3), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                detail.LinkmanId = reader.GetInt32(4);

                if (!details.Contains(detail.LinkmanId))
                {
                    details[detail.LinkmanId] = new List<LinkmanDetails>();
                }
                ((List<LinkmanDetails>)details[detail.LinkmanId]).Add(detail);
            }

            return details;
        }

        public static List<LinkmanDetails> listAllLinkmanDetailsFromDB()
        {
            SQLiteCommand cmd = new SQLiteCommand(" SELECT * FROM "
                + TABLE_LINKMAN_DETAILS + " ; ", BASE_SQLITE_CONNECTION);
            SQLiteDataReader reader = cmd.ExecuteReader();

            LinkmanDetails detail = null;
            List<LinkmanDetails> details = new List<LinkmanDetails>();

            while (reader.Read())
            {
                detail = new LinkmanDetails();
                detail.ID = reader.GetInt32(0);
                detail.Type = reader.GetInt32(1);
                detail.Info = EncryptDecryptUtils.DES3Decrypt(reader.GetString(2), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                detail.Remark = EncryptDecryptUtils.DES3Decrypt(reader.GetString(3), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                detail.LinkmanId = reader.GetInt32(4);
                details.Add(detail);
            }

            return details;
        }

        public static Hashtable listAllAccount()
        {
            SQLiteCommand cmd = new SQLiteCommand(" SELECT * FROM "
                + TABLE_ACCOUNT + " ; ", BASE_SQLITE_CONNECTION);
            SQLiteDataReader reader = cmd.ExecuteReader();

            Account account = null;
            Hashtable list = new Hashtable();

            while (reader.Read())
            {
                account = new Account();
                account.ID = reader.GetInt32(0);
                account.Title = EncryptDecryptUtils.DES3Decrypt(reader.GetString(1), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                account.Name = EncryptDecryptUtils.DES3Decrypt(reader.GetString(2), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                account.Password = EncryptDecryptUtils.DES3Decrypt(reader.GetString(3), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                account.Remark = EncryptDecryptUtils.DES3Decrypt(reader.GetString(4), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                account.GroupId = reader.GetInt32(5);

                if (!list.Contains(account.GroupId))
                {
                    list[account.GroupId] = new List<Account>();
                }
                ((List<Account>)list[account.GroupId]).Add(account);
            }

            return list;
        }

        public static List<Account> listAllAccountFromDB()
        {
            SQLiteCommand cmd = new SQLiteCommand(" SELECT * FROM "
                + TABLE_ACCOUNT + " ; ", BASE_SQLITE_CONNECTION);
            SQLiteDataReader reader = cmd.ExecuteReader();

            Account account = null;
            List<Account> list = new List<Account>();

            while (reader.Read())
            {
                account = new Account();
                account.ID = reader.GetInt32(0);
                account.Title = EncryptDecryptUtils.DES3Decrypt(reader.GetString(1), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                account.Name = EncryptDecryptUtils.DES3Decrypt(reader.GetString(2), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                account.Password = EncryptDecryptUtils.DES3Decrypt(reader.GetString(3), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                account.Remark = EncryptDecryptUtils.DES3Decrypt(reader.GetString(4), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                account.GroupId = reader.GetInt32(5);
                list.Add(account);
            }

            return list;
        }

        public static List<AccountGroup> listAllAccountGroup()
        {
            SQLiteCommand cmd = new SQLiteCommand(" SELECT * FROM "
                + TABLE_ACCOUNT_GROUP + " ; ", BASE_SQLITE_CONNECTION);
            SQLiteDataReader reader = cmd.ExecuteReader();

            AccountGroup group = null;
            List<AccountGroup> list = new List<AccountGroup>();

            while (reader.Read())
            {
                group = new AccountGroup();
                group.ID = reader.GetInt32(0);
                group.Title = EncryptDecryptUtils.DES3Decrypt(reader.GetString(1), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                group.Remark = EncryptDecryptUtils.DES3Decrypt(reader.GetString(2), EncryptDecryptUtils.GLOBAL_INIT_VAR);
                list.Add(group);
            }

            return list;
        }

        #endregion

        #region delete all

        public static bool deleteAllNote()
        {
            int ret = 0;
            using (SQLiteTransaction tran = BASE_SQLITE_CONNECTION.BeginTransaction())
            {
                SQLiteCommand cmd = new SQLiteCommand(" DELETE FROM "
                    + TABLE_NOTE + " ; ", BASE_SQLITE_CONNECTION);
                ret = cmd.ExecuteNonQuery();
                tran.Commit();
            }
            return ret != 0;
        }

        public static bool deleteAllDictionary()
        {
            int ret = 0;
            using (SQLiteTransaction tran = BASE_SQLITE_CONNECTION.BeginTransaction())
            {
                SQLiteCommand cmd = new SQLiteCommand(" DELETE FROM "
                    + TABLE_DICTIONARY + " ; ", BASE_SQLITE_CONNECTION);
                ret = cmd.ExecuteNonQuery();
                tran.Commit();
            }
            return ret != 0;
        }

        public static bool deleteAllTools(bool isDeleteGroup)
        {
            int ret = 0;
            using (SQLiteTransaction tran = BASE_SQLITE_CONNECTION.BeginTransaction())
            {
                SQLiteCommand cmd = new SQLiteCommand(" DELETE FROM "
                    + TABLE_TOOL + " ; ", BASE_SQLITE_CONNECTION);
                ret += cmd.ExecuteNonQuery();

                if (isDeleteGroup)
                {
                    cmd.CommandText = " DELETE FROM " + TABLE_TOOL_GROUP + " ; ";
                    ret += cmd.ExecuteNonQuery();
                }
                tran.Commit();
            }
            return ret != 0;
        }

        public static bool deleteAllLinkman(bool isDeleteGroup)
        {
            int ret = 0;
            using (SQLiteTransaction tran = BASE_SQLITE_CONNECTION.BeginTransaction())
            {
                SQLiteCommand cmd = new SQLiteCommand(" DELETE FROM "
                    + TABLE_LINKMAN + " ; ", BASE_SQLITE_CONNECTION);
                ret += cmd.ExecuteNonQuery();

                cmd.CommandText = " DELETE FROM " + TABLE_LINKMAN_DETAILS + " ; ";
                ret += cmd.ExecuteNonQuery();

                if (isDeleteGroup)
                {
                    cmd.CommandText = " DELETE FROM " + TABLE_LINKMAN_GROUP + " ; ";
                    ret += cmd.ExecuteNonQuery();
                }
                tran.Commit();
            }
            return ret != 0;
        }

        public static bool deleteAllAccount(bool isDeleteGroup)
        {
            int ret = 0;
            using (SQLiteTransaction tran = BASE_SQLITE_CONNECTION.BeginTransaction())
            {
                SQLiteCommand cmd = new SQLiteCommand(" DELETE FROM "
                    + TABLE_ACCOUNT + " ; ", BASE_SQLITE_CONNECTION);
                ret += cmd.ExecuteNonQuery();

                if (isDeleteGroup)
                {
                    cmd.CommandText = " DELETE FROM " + TABLE_ACCOUNT_GROUP + " ; ";
                    ret += cmd.ExecuteNonQuery();
                }
                tran.Commit();
            }
            return ret != 0;
        }

        #endregion

    }
}
